Now that you have seen the basic principles and techniques of model-driven apps, you can take the opportunity to use this knowledge to step through creating your own model-driven app to track sales orders.

In earlier lessons, you learned about the Microsoft Dataverse. A key feature of model-driven apps is that they can only be based on data stored in the Dataverse. They cannot draw directly from files stored in a OneDrive or a SharePoint site.

You heard in a previous lesson that the Dataverse is already populated with standard “out-of-the-box” table structures. In the video in that lesson, you saw the demonstrator use one of these, the Accounts table, and show the column headings that were automatically included in that table.

However, the question you are probably asking is how can you add your own data to the Dataverse? There are several options. A quick choice would be to customize any of the standard tables such as the Accounts table and add your own data.

An alternative approach is to create tables from scratch within the Dataverse. To create a table, select the Data option on the navigation bar on the left of the Power Apps screen. Clicking on tables triggers a customized toolbar across the top of the screen which has a +New Table option. When you create a new table, Microsoft populates it with many standard columns. You can decide which columns headings want to use and simply ignore those that are not relevant. You can also add your own custom columns to a new table if you need more specific information and headings.

But what about a situation where you already have large blocks of data contained in files in your local drive, your OneDrive or on a SharePoint site? Information can be drawn out of these files and placed in tables in the Dataverse by using the connectors that you heard about earlier. For this exercise, you are going to bring information from an Excel file into a table in the Dataverse and then use the table to create a model-driven app.

Bringing Excel Data into a Table in the Dataverse

1.     Download the sample file Equipment Orders.xlsx to your local drive.

2.     Run Power Apps from the app launcher on your 365 home page or go to https://make.powerapps.com and sign in with your organizational account.

3.     In the Power Apps screen, click on Dataverse on the navigation bar on the left and then click on Tables.

4.     On the toolbar across the top, click on the Data drop-down and then click directly on the Get Data option.

5.     You are presented with a list of connectors. Choose the Excel Workbook connector. When you do, a dialog called Connect to Data Source appears and you should see the heading Connection Settings.

6.     Notice the radio button option under the heading which allows you to either link to the file or upload it. Choose upload.

7.     You are now presented with a message box that says “Drag a file here to upload it” It also contains a browse button.

8.     Use the browse option to navigate to where you stored the Equipment Orders file. Select it and click on next. You will be asked to sign in again to authenticate your credentials. Once you have been authenticated, click on the Next button.

9.     In the Choose Data dialog you will see the Excel file contents listed on the left. Select the Orders_Table table. Click next.

Power Query

10.  The program that is controlling the import is called Power Query and it will now show you a sample of the data that it has found in the source. Power Query allows you to make changes to the layout and format of data that you are importing. For example, you can see that the ribbon contains options such as Choose Columns and Choose Rows. There are also options to remove columns or rows that you do not want to import.

11.  For now, you will be loading the table without any changes. Click next.

12.  In the next dialog box, Map Tables, there are two steps you will need to take to ensure that the rest of this exercise can be completed as outlined.

Click next.

13.  In the refresh settings window, choose “refresh manually” but note that you could also ask for this table to be refreshed automatically. Click on publish.

Well done! The data from the Excel file has now been loaded into a table in your Dataverse. Don’t worry if you do not immediately see it listed in the Tables list in Power Apps as this list can sometimes take some time to refresh. You might need to wait for the table to be created in the background or you might need to hit F5 one or more times to refresh the list before you see the new Order Table entry appear.

What is a Data Model?

Model-driven apps can hold information from multiple sources.

For example, you can design an app that uses and displays data from several tables by creating a relationship between those tables so that the app can move from one table to the other accessing data.

You can also include other elements and steps in the app that are required in a business process, such as order fulfillment. In a future lesson, you will look at automated Business Flows which guide a user through a series of steps or a process. These Business Flows can also be incorporated into a model-driven app.

This combination of information and process is known as a Data Model, a concept you may be familiar with from databases such as Access or a business analytics service such as Power BI. The Data Model underpins the app.